import pymysql
import pandas as pd
from datetime import datetime, timedelta
from db_conn.dbConn import conn_db,conn_db_engine
import pandas as pd
import time
from sqlalchemy import create_engine, text
from db_conn.dbConn import conn_db_engine
conn1 = conn_db_engine('iot_local')
conn2 = conn_db_engine('ryerp_local')
connect = conn_db('ryerp_local')

def insert_machine_production(current_time):
    morning_time = current_time.replace(hour=7, minute=0, second=0, microsecond=0)
    evening_time = current_time.replace(hour=19, minute=0, second=0, microsecond=0)
    yesterday_morning_time = morning_time - pd.DateOffset(days=1)
    yesterday_evening_time = evening_time - pd.DateOffset(days=1)
    # 检查当前时间是在早上8点和晚上8点之间，还是在晚上8点之后
    if current_time >= evening_time:
        # 如果当前时间在晚上8点之后，那么前一个时间点是晚上8点
        start_time = morning_time
        end_time = evening_time
        production_date = current_time.date()
        daynight = '白班'
    elif current_time >= morning_time:
        # 如果当前时间在早上8点和晚上8点之间，那么前一个时间点是早上8点
        start_time = yesterday_evening_time
        end_time = morning_time
        # production_date 为 昨天
        production_date = yesterday_morning_time.date()
        daynight = '夜班'
    elif current_time < morning_time:
        # 如果当前时间在早上8点之前，那么前一个时间点是昨天晚上8点
        start_time = yesterday_morning_time
        end_time = yesterday_evening_time
        # production_date 为 昨天
        production_date = yesterday_morning_time.date()
        daynight = '白班'


    df = pd.read_sql("SELECT device_id,goods_id,goods_style,batch_id,finished_size FROM mm_new_device", conn2)
    # 获取该时间段数据
    sql1 = 'SELECT * FROM iotdemo WHERE receive_time >= %s and receive_time < %s'
    # 使用Pandas的read_sql函数执行SQL语句并获取数据
    df_iot = pd.read_sql(sql1, con=conn1, params=(start_time,end_time))
    df_iot = df_iot[df_iot['type']=='1']
    #去掉df_iot中grp为6，dev为144的数据
    df_iot = df_iot[~((df_iot['grp'] == 6) & (df_iot['dev'] == 144))]
    #去掉df_iot中dev为169,170,171且num<20000的数据
    df_iot = df_iot[~(((df_iot['dev'] == 169) | (df_iot['dev'] == 170) | (df_iot['dev'] == 171)) & (df_iot['num'] > 20000))]

    #-----------------计算产量---------------------
    # 获取每个dev的产量,方法是找到每个dev，最大的receive_time和最小的receive_time,然后计算两者num差值
    df_iot['receive_time'] = pd.to_datetime(df_iot['receive_time'])
    # 找到每个开发人员最早和最晚的接收时间对应的 num 值
    min_num_by_dev = df_iot.loc[df_iot.groupby('dev')['receive_time'].idxmin(), ['dev', 'num']]
    max_num_by_dev = df_iot.loc[df_iot.groupby('dev')['receive_time'].idxmax(), ['dev', 'num']]
    # 合并这两个 DataFrame
    production_df = pd.merge(min_num_by_dev, max_num_by_dev, on='dev', suffixes=('_min', '_max'))
    # 计算产量
    production_df['production'] = production_df['num_max'] - production_df['num_min']
    # 以双为单位，除以2四舍五入
    production_df['production'] = production_df['production'].apply(lambda x: x//2+1)
    # dev改成device_id
    production_df = production_df.rename(columns={'dev': 'device_id'})

    df = pd.merge(df, production_df[['device_id', 'production']], on='device_id', how='left')

    # ---------------计算平均时长------------------
    df_sock_time = df_iot[df_iot['type'] > '0']
    def filter_consecutive_type_1(group):
        # 创建一个新列，表示上一行的type
        group['prev_type'] = group['type'].shift(1)
        group['prev_num'] = group['num'].shift(1)
        group['prev_receive_time'] = group['receive_time'].shift(1)
        # 筛选出连续两行type都为1的行
        return group[(group['type'] == '1') & (group['prev_type'] == '1')]

    # 应用上述函数
    df_sock_time = df_sock_time.groupby('dev').apply(filter_consecutive_type_1).drop(columns=['prev_type'])
    df_sock_time = df_sock_time.reset_index(drop=True)
    df_sock_time['avg_time'] = df_sock_time.apply(
        lambda row: (row['receive_time'] - row['prev_receive_time']).total_seconds() / (row['num'] - row['prev_num']) if
        row['num'] != row['prev_num'] else 0, axis=1)
    # 去除异常数据
    df_sock_time['avg_time'] = df_sock_time['avg_time'].apply(lambda x: x if x < 210 and x > 15 else -1.0)
    df_sock_time = df_sock_time[df_sock_time['avg_time'] != -1.0]
    df_sock_time = df_sock_time[['dev', 'avg_time']].groupby('dev').mean().reset_index()
    # dev改成device_id
    df_sock_time = df_sock_time.rename(columns={'dev': 'device_id'})
    df = pd.merge(df, df_sock_time, on='device_id', how='left')
    df['production_date'] = production_date
    df['daynight'] = daynight
    print(df['batch_id'])
    df.to_sql('mm_new_weave_production_machine', conn2 , if_exists='append', index=False)
    next_update_time = end_time + timedelta(hours=12)
    print(next_update_time)
    #将next_update_time写入sys_update_time表中update_object = machineProduction的update_time中
    with connect.cursor() as cursor:
        cursor.execute("UPDATE sys_update_time SET update_time = %s WHERE update_object = 'machineProduction'",(next_update_time,))
        # 提交事务
        connect.commit()
# 添加日期和日夜班

while True:
    current_time = datetime.now()
    print(current_time)
    #找出sys_update_time表中update_object = machineProduction的update_time 设为update_time
    query = text("SELECT update_time FROM sys_update_time WHERE update_object = 'machineProduction'")

    # 使用连接执行查询
    with conn2.connect() as connection:
        result = connection.execute(query)
        update_time = result.fetchone()[0]

        # 将update_time转换为datetime对象
    print(update_time)
    if current_time > update_time:
        try:
            insert_machine_production(current_time)
        except Exception as e:
            print(f"An error occurred: {e}")
    df = pd.read_sql_query("SELECT * FROM mm_new_weave_production_machine", conn2)
    # 去重重复的行



    df = df.drop_duplicates(subset=['device_id', 'production_date', 'daynight'])
    sql2 = 'DELETE FROM mm_new_weave_production_machine'
    cursor = connect.cursor()
    cursor.execute(sql2)
    connect.commit()
    print(df)
    df.to_sql('mm_new_weave_production_machine',conn2,if_exists='append',index=False)
    print('waiting-----------')
    time.sleep(600)


